Release 10.1A: OpenEdge Data Management:
DataServer for ORACLE

Writing queries for performance

This section provides a collection of tips and guidelines to follow when writing queries. For example, a query that processes a large number of rows performs best if it uses NO-LOCK, lookahead cursors, a large cache size, and a small field list. The following suggestions might help improve the performance of your DataServer applications. Try some of the following and use the DEBUG diagnostic options to gather statistics on how your application runs:

Use FOR EACH, GET, and OPEN QUERY statements as opposed to FIND statements, which generally perform more slowly. Consider using the FOR FIRST statement instead of FIND FIRST. The only exception is that FIND LAST is faster than GET LAST because GET LAST causes the client to process all the records. The FIND LAST statement allows the server to retrieve the last record.
Take advantage of field lists.
Take advantage of the QUERY-TUNING options.
Use lookahead cursors.
Use NO-LOCK where possible.
Avoid specifying lock upgrades. Allow the DataServer and ORACLE to handle lock upgrades.
Do not ask for a particular ordering of results with USE-INDEX or BY clauses, unless your application requires it. Allow the DataServer and ORACLE to determine which index (if any) is most efficient for processing a query and avoid the overhead of sorting results.
For aggregates, use the RUN-STORED-PROC send-sql-statement syntax or use a Progress SQL statement. If you use a Progress SQL statement with a cursor, declare the cursor read-only.
If you are testing for the existence of a record, use the CAN-FIND function, which does not retrieve the record if the DataServer passes the entire WHERE clause to ORACLE for processing. However, avoid nesting CAN-FIND functions.
Avoid using the RECID function. Use the ROWID function.

See the "Analyzing performance" section for information on collecting statistics.